package com.dha.dao.impl;

import java.util.List;

import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.dha.custom.GiaoVienCustom;
import com.dha.custom.XemThoiKhoaBieuCustom;
import com.dha.dao.XemThoiKhoaBieuDAO;
import com.dha.utility.Utility;

@WebService(targetNamespace = "XemThoiKhoaBieu")
public class XemThoiKhoaBieuDAOImpl implements XemThoiKhoaBieuDAO {

	private SessionFactory sessionFactory = new Configuration().configure()
			.buildSessionFactory();
	private Session session;

	@WebMethod(operationName="layDanhSachHocKyHienHoat")
	@Override
	public List<String> layDanhSachHocKyHienHoat(@WebParam(name="MaSo") String maSo) {
		return Utility.danhSachHocKy(maSo);
	}

	@Override
	@WebMethod(operationName="XemThoiKhoaBieuHocKyHienTai")
	public List<XemThoiKhoaBieuCustom> xemThoiKhoaBieu(@WebParam(name="MaSo")String maSo) {
		try {
			int hocKyHienTai = Utility.layMaHocKyHienTai(maSo);
			session = sessionFactory.openSession();
			session.beginTransaction();
			String sql = "";
			if(maSo.length() == 12){
				sql = "select distinct new com.dha.custom.XemThoiKhoaBieuCustom(lhpe.maLopHocPhan, hpe.tenHocPhan, "
						+ "lhpe.thu, lhpe.tuTiet, lhpe.denTiet, gve.tenGiaoVien, lhpe.ngayHieuLuc, lhpe.ghiChu, lhpe.phong) "
						+ "from LopHocPhanEntity lhpe, HocPhanEntity hpe, GiaoVienEntity gve, "
						+ "SinhVienEntity sve, LopHocPhanSinhVienEntity lhpsve "
						+ "where lhpe.maHocPhan = hpe.maHocPhan and gve.maGiaoVien = lhpe.maGiaoVien "
						+ "and lhpsve.maLopHocPhan = lhpe.maLopHocPhan and lhpsve.maSinhVien = ? "
						+ "and lhpe.hocKy = ? " + "order by lhpe.thu ASC";
			}else{
				if(maSo.length() == 7){
					sql = "select distinct new com.dha.custom.XemThoiKhoaBieuCustom(lhpe.maLopHocPhan, hpe.tenHocPhan, lhpe.thu, lhpe.tuTiet, lhpe.denTiet, gve.tenGiaoVien, lhpe.ngayHieuLuc, lhpe.ghiChu, lhpe.phong) "
							+ "from LopHocPhanEntity lhpe, HocPhanEntity hpe, GiaoVienEntity gve, "
							+ "SinhVienEntity sve, LopHocPhanSinhVienEntity lhpsve "
							+ "where lhpe.maHocPhan = hpe.maHocPhan "
							+ "and gve.maGiaoVien = lhpe.maGiaoVien and lhpe.maGiaoVien = ? "
							+ "and lhpe.hocKy = ? " + "order by lhpe.thu ASC";
				}else{
					return null;
				}
			}
			
			Query query = session.createQuery(sql);
			query.setParameter(0, maSo);
			query.setParameter(1, hocKyHienTai);
			session.getTransaction().commit();
			return query.list();
		} catch (Exception e) {
			session.getTransaction().rollback();
			return null;
		}
		finally{
			session.close();
		}
	}

	@Override
	@WebMethod(operationName="XemThoiKhoaBieuTheoHocKy")
	public List<XemThoiKhoaBieuCustom> xemThoiKhoaBieuTheoHocKy(
			@WebParam(name="MaSo") String maSo,@WebParam(name="HocKy") int hocKy) {
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			String sql = "";
			if(maSo.length() == 12){
				sql = "select distinct new com.dha.custom.XemThoiKhoaBieuCustom(lhpe.maLopHocPhan, hpe.tenHocPhan, "
						+ "lhpe.thu, lhpe.tuTiet, lhpe.denTiet, gve.tenGiaoVien, lhpe.ngayHieuLuc, lhpe.ghiChu, lhpe.hocKy) "
						+ "from LopHocPhanEntity lhpe, HocPhanEntity hpe, GiaoVienEntity gve, "
						+ "SinhVienEntity sve, LopHocPhanSinhVienEntity lhpsve "
						+ "where lhpe.maHocPhan = hpe.maHocPhan and gve.maGiaoVien = lhpe.maGiaoVien "
						+ "and lhpsve.maLopHocPhan = lhpe.maLopHocPhan and lhpsve.maSinhVien = ? "
						+ "and lhpe.hocKy = ? " + "order by lhpe.ngayHieuLuc desc";
			}else{
				if(maSo.length() == 7){
					sql = "select distinct new com.dha.custom.XemThoiKhoaBieuCustom(lhpe.maLopHocPhan, hpe.tenHocPhan, lhpe.thu, lhpe.tuTiet, lhpe.denTiet, gve.tenGiaoVien, lhpe.ngayHieuLuc, lhpe.ghiChu, lhpe.hocKy) "
							+ "from LopHocPhanEntity lhpe, HocPhanEntity hpe, GiaoVienEntity gve, "
							+ "SinhVienEntity sve, LopHocPhanSinhVienEntity lhpsve "
							+ "where lhpe.maHocPhan = hpe.maHocPhan "
							+ "and gve.maGiaoVien = lhpe.maGiaoVien and lhpe.maGiaoVien = ? "
							+ "and lhpe.hocKy = ? " + "order by lhpe.ngayHieuLuc desc";
				}else{
					return null;
				}
			}
			
			Query query = session.createQuery(sql);
			query.setParameter(0, maSo);
			query.setParameter(1, hocKy);
			session.getTransaction().commit();
			return query.list();
		} catch (Exception e) {
			session.getTransaction().rollback();
			return null;
		}
		finally{
			session.close();
		}
	}
	@WebMethod(operationName="XemThoiKhoaBieuCaKhoaHoc")
	@Override
	public List<XemThoiKhoaBieuCustom> xemThoiKhoaBieuToanKhoa(@WebParam(name="MaSo") String maSo) {
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			String sql = "";
			if(maSo.length() == 12){
				sql = "select distinct new com.dha.custom.XemThoiKhoaBieuCustom(lhpe.maLopHocPhan, hpe.tenHocPhan, "
						+ "lhpe.thu, lhpe.tuTiet, lhpe.denTiet, gve.tenGiaoVien, lhpe.ngayHieuLuc, lhpe.ghiChu, lhpe.hocKy) "
						+ "from LopHocPhanEntity lhpe, HocPhanEntity hpe, GiaoVienEntity gve, "
						+ "SinhVienEntity sve, LopHocPhanSinhVienEntity lhpsve "
						+ "where lhpe.maHocPhan = hpe.maHocPhan and gve.maGiaoVien = lhpe.maGiaoVien "
						+ "and lhpsve.maLopHocPhan = lhpe.maLopHocPhan and lhpsve.maSinhVien = ? "
						+ "order by lhpe.ngayHieuLuc desc";
			}else{
				if(maSo.length() == 7){
					sql = "select distinct new com.dha.custom.XemThoiKhoaBieuCustom(lhpe.maLopHocPhan, hpe.tenHocPhan, lhpe.thu, lhpe.tuTiet, lhpe.denTiet, gve.tenGiaoVien, lhpe.ngayHieuLuc, lhpe.ghiChu, lhpe.hocKy) "
							+ "from LopHocPhanEntity lhpe, HocPhanEntity hpe, GiaoVienEntity gve, "
							+ "SinhVienEntity sve, LopHocPhanSinhVienEntity lhpsve "
							+ "where lhpe.maHocPhan = hpe.maHocPhan "
							+ "and gve.maGiaoVien = lhpe.maGiaoVien and lhpe.maGiaoVien = ? "
							+ "order by lhpe.ngayHieuLuc desc";

				}else{
					return null;
				}
			}
			
			Query query = session.createQuery(sql);
			query.setParameter(0, maSo);
			session.getTransaction().commit();
			return query.list();
		} catch (Exception e) {
			session.getTransaction().rollback();
			return null;
		}
		finally{
			session.close();
		}
	}

	


}
